﻿using System;
using System.Collections;
using System.Data;
using System.IO;
using SQLiteWrapper;

namespace NetTrafficStatistics
{
	class SqLiteDb
	{
		private static SQLiteBase Db;
		private const string Filename = "traffic.db";
		private const string ConfigTable = "config";
		private const string TrafficTable = "traffic";

		private static DateTime CacheTime;
		private static Tools.Zone CacheZone;
		private static Hashtable CacheData = new Hashtable {{"input", 0}, {"output", 0}};
		
		private const string DateTimeFormat = "yyyy-MM-dd HH:00";

		public static void Start()
		{
			Db = new SQLiteBase (Filename);
			var arr = Db.GetTables();
			CreateTables (arr);
			CheckForCache();
		}

		public static void Close ()
		{
			InsertTrafficValues (CacheTime, CacheZone, CacheData);
			Db.CloseDatabase ();
		}

		private static void CreateTables (ArrayList arr)
		{
			if (arr.IndexOf (ConfigTable) == -1)
				Db.ExecuteNonQuery (@"CREATE TABLE '" + ConfigTable + "'('name' TEXT, 'data' TEXT)");

			if (arr.IndexOf (TrafficTable) == -1)
			{
				var sql = string.Format (@"CREATE TABLE '{0}'('time' datetime null, 'zone' INTEGER, 'input' INTEGER, 'output' INTEGER)", TrafficTable);
				var sqlCreateindex = string.Format ("CREATE UNIQUE INDEX zone_time_indx ON '{0}' (time, zone)", TrafficTable);

				Db.ExecuteNonQuery (sql);
				Db.ExecuteNonQuery (sqlCreateindex);
			}
		}

		public static ArrayList ReadTable (Params p)
		{
			long input = 0;
			long output = 0;
			long inputMax = 1;
			long outputMax = 1;
			var arr = new ArrayList ();
			const string sql = "SELECT time, SUM(input) as i, SUM(output) as o {0} FROM {1} WHERE {2} GROUP by {3}";
			var result = Db.ExecuteQuery (string.Format (sql, p.SelectEx, TrafficTable, p.Where, p.GroupBy));
			if (result.Rows.Count > 0)
			{
				foreach (DataRow row in result.Rows)
				{
					var i = Int64.Parse (row["i"].ToString());
					var o = Int64.Parse (row["o"].ToString());
					arr.Add (new ArrayList { row["time"], i, o });

					input += i;
					output += o;
					inputMax = Math.Max (inputMax, i);
					outputMax = Math.Max (outputMax, o);
				}
				return new ArrayList { arr, new[] { 0, input, output }, new[] { 0, inputMax, outputMax } };
			}
			return null;
		}

		public static void InsertTrafficValues (DateTime dt, Tools.Zone zone, Hashtable data)
		{
			if (dt.Ticks > 0)
			{
				var time = dt.ToString (DateTimeFormat);
				var where = string.Format ("time='{0}' AND zone = {1}", time, (int) zone);
				var insert = string.Format ("'{0}',{1},{2},{3}", time, (int) zone, data["input"], data["output"]);

				Db.InsertOrUpdate (data, TrafficTable, where, insert);
				Tools.Counter++;
			}

			if (File.Exists (Tools.TmpFile))
			{
				File.Delete (Tools.TmpFile);
			}
		}

		public static string ExportTableCsv ()
		{
			var list = "";
			var result = Db.ExecuteQuery (string.Format ("SELECT * FROM {0} ORDER by time ASC, zone ASC", TrafficTable));
			if (result.Rows.Count > 0)
			{
				foreach (DataColumn c in result.Columns)
				{
					list += c.ColumnName + ";";
				}
				list += Environment.NewLine;

				foreach (DataRow row in result.Rows)
				{
					foreach (var v in row.ItemArray)
					{
						list += v + ";";
					}
					list += Environment.NewLine;
				}
				return list;
			}
			return "";
		}

		/// <summary>
		/// Special cache function
		/// </summary>
		/// <param name="time"></param>
		/// <param name="zone"></param>
		/// <param name="directing"></param>
		/// <param name="value"></param>
		public static void Save (DateTime time, Tools.Zone zone, string directing, int value)
		{
			if (CacheTime.Ticks > 0 && ((CacheTime.Minute != time.Minute) || (CacheZone != zone)))
			{
				InsertTrafficValues (CacheTime, CacheZone, CacheData);
				CacheData = new Hashtable {{"input", 0}, {"output", 0}};
			}

			CacheTime = time;
			CacheZone = zone;
			CacheData[directing] = (int) CacheData[directing] + value;

			if ((CacheTime.Second != time.Second) || (CacheZone != zone))
			{
				File.WriteAllText (Tools.TmpFile, String.Format ("{0},{1},{2},{3}", CacheTime, (int) CacheZone, CacheData["input"], CacheData["output"]));
			}
		}

		/// <summary>
		/// Save existing cache data on startup
		/// </summary>
		private static void CheckForCache ()
		{
			try
			{
				if (File.Exists (Tools.TmpFile))
				{
					var data = File.ReadAllText (Tools.TmpFile).Split (',');

					var t = DateTime.Parse (data[0]);
					var z = (Tools.Zone) Int32.Parse (data[1]);
					var i = Int32.Parse (data[2]);
					var o = Int32.Parse (data[3]);

					InsertTrafficValues (t, z, new Hashtable { { "input", i }, { "output", o } });
				}
			}
			catch (Exception e)
			{
				Tools.WriteLogs ("Cache parsing error");
				Tools.WriteLogs (e.ToString());
			}

		}
	}
}
